﻿-- =============================================
-- Author:		<赵洪涛>
-- Create date: <2012-03-12>
-- Description:	<按指定的日期、公司取对应的余额、业务额>
-- =============================================
CREATE PROCEDURE [dbo].[proc_AB_GetList_Out] 
(
		@zhjb INT,
		@InCompanyId varchar(250),
		@StartDate varchar(10),
		@StartIndex int,
		@EndIndex int
)
AS
BEGIN
Declare @cSql varchar(2000)

Set @cSql = 'CompanyId In('+@InCompanyId+')'

IF @zhjb>0
BEGIN
	IF @zhjb=1
	SET @cSql = @cSql+' and syzh=0'
	IF @zhjb=2
	SET @cSql = @cSql+' and syzh<>0'
END
	

Set @cSql=';WITH list As(Select ROW_NUMBER() OVER (Order By syzh,CompanyId,id) AS Row,
CompanyId,Companyname,Productname,syzh,id
,IsNull((Select b.je From AB_List b Where b.Iid=a.id And Convert(varchar(10),b.UpTime,120)='''+@StartDate+''' And AB_Type=0),0) As je		--当日业务总额
,IsNull((Select Top 1 c.ye From AB_List c Where c.Iid=a.id And Convert(varchar(10),c.UpTime,120)<='''+@StartDate+''' And AB_Type=0 Order By c.UpTime Desc),0) As ye		--当日余额



,IsNull((Select d.Items From AB_List d Where d.Iid=a.id And Convert(varchar(10),d.UpTime,120)='''+@StartDate+''' And AB_Type=0),0) As Items		--当日业务笔数
From AB_Interface a
Where '+@cSql+' 

)
Select *,(Select Count(0) From list) As RecordCount From list Where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex)+' Order By Row'
--PRINT(@cSql)
Exec(@cSql)
END
